{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 原创：周志鹏\n",
    "### 公众号：数据不吹牛，更多案例和有趣分析等你来撩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('C:\\\\Users\\\\Administrator\\\\Desktop\\\\JC数据集')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>品牌名称</th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>付款日期</th>\n",
       "      <th>订单状态</th>\n",
       "      <th>实付金额</th>\n",
       "      <th>邮费</th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>购买数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>viva la vida</td>\n",
       "      <td>做快淘饭</td>\n",
       "      <td>2019-04-18 00:03:00</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>22.32</td>\n",
       "      <td>0</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>viva la vida</td>\n",
       "      <td>作自有世祟</td>\n",
       "      <td>2019-02-17 00:03:51</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>87.00</td>\n",
       "      <td>0</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>viva la vida</td>\n",
       "      <td>作雪白室</td>\n",
       "      <td>2019-04-18 00:01:43</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>97.66</td>\n",
       "      <td>0</td>\n",
       "      <td>福建省</td>\n",
       "      <td>福州市</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>viva la vida</td>\n",
       "      <td>作美女购物主</td>\n",
       "      <td>2019-01-11 23:35:01</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>37.23</td>\n",
       "      <td>0</td>\n",
       "      <td>河南省</td>\n",
       "      <td>安阳市</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>viva la vida</td>\n",
       "      <td>作美女购物主</td>\n",
       "      <td>2019-02-18 14:16:03</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>29.50</td>\n",
       "      <td>0</td>\n",
       "      <td>河南省</td>\n",
       "      <td>安阳市</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           品牌名称    买家昵称                付款日期  订单状态   实付金额  邮费   省份   城市  购买数量\n",
       "0  viva la vida    做快淘饭 2019-04-18 00:03:00  交易成功  22.32   0   北京  北京市     1\n",
       "1  viva la vida   作自有世祟 2019-02-17 00:03:51  交易成功  87.00   0   上海  上海市     1\n",
       "2  viva la vida    作雪白室 2019-04-18 00:01:43  交易成功  97.66   0  福建省  福州市     2\n",
       "3  viva la vida  作美女购物主 2019-01-11 23:35:01  交易成功  37.23   0  河南省  安阳市     3\n",
       "4  viva la vida  作美女购物主 2019-02-18 14:16:03  交易成功  29.50   0  河南省  安阳市     2"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('TGI指数案例数据.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据概览"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 28832 entries, 0 to 28831\n",
      "Data columns (total 9 columns):\n",
      "品牌名称    28832 non-null object\n",
      "买家昵称    28832 non-null object\n",
      "付款日期    28832 non-null datetime64[ns]\n",
      "订单状态    28832 non-null object\n",
      "实付金额    28832 non-null float64\n",
      "邮费      28832 non-null int64\n",
      "省份      28832 non-null object\n",
      "城市      28832 non-null object\n",
      "购买数量    28832 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算单个用户平均支付金额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>实付金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>49.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>22.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>34.570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>37.475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>13.500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称    实付金额\n",
       "0   .blue_ram  49.450\n",
       "1  .christiny  22.000\n",
       "2     .willn1  34.570\n",
       "3        .托托m  37.475\n",
       "4       0000妮  13.500"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp_user = df.groupby('买家昵称')['实付金额'].mean().reset_index()\n",
    "gp_user.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 基于用户支付金额，判断用户是属于低客单还是高客单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>实付金额</th>\n",
       "      <th>客单类别</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>49.450</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>22.000</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>34.570</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>37.475</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>13.500</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0009797王</td>\n",
       "      <td>94.500</td>\n",
       "      <td>高客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>000xyx0</td>\n",
       "      <td>99.250</td>\n",
       "      <td>高客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>000米粒儿米粒0</td>\n",
       "      <td>24.500</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>00556旭79618</td>\n",
       "      <td>23.860</td>\n",
       "      <td>低客单</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>00不哭0</td>\n",
       "      <td>53.545</td>\n",
       "      <td>高客单</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          买家昵称    实付金额 客单类别\n",
       "0    .blue_ram  49.450  低客单\n",
       "1   .christiny  22.000  低客单\n",
       "2      .willn1  34.570  低客单\n",
       "3         .托托m  37.475  低客单\n",
       "4        0000妮  13.500  低客单\n",
       "5     0009797王  94.500  高客单\n",
       "6      000xyx0  99.250  高客单\n",
       "7    000米粒儿米粒0  24.500  低客单\n",
       "8  00556旭79618  23.860  低客单\n",
       "9        00不哭0  53.545  高客单"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def if_high(x):\n",
    "    if x > 50:\n",
    "        return '高客单'\n",
    "    else:\n",
    "        return '低客单'\n",
    "\n",
    "gp_user['客单类别'] = gp_user['实付金额'].apply(if_high)\n",
    "gp_user.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将客单数据和地域数据合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家昵称</th>\n",
       "      <th>实付金额_x</th>\n",
       "      <th>客单类别</th>\n",
       "      <th>品牌名称</th>\n",
       "      <th>付款日期</th>\n",
       "      <th>订单状态</th>\n",
       "      <th>实付金额_y</th>\n",
       "      <th>邮费</th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>购买数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.blue_ram</td>\n",
       "      <td>49.450</td>\n",
       "      <td>低客单</td>\n",
       "      <td>viva la vida</td>\n",
       "      <td>2019-02-04 17:49:34.000</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>49.450</td>\n",
       "      <td>0</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>.christiny</td>\n",
       "      <td>22.000</td>\n",
       "      <td>低客单</td>\n",
       "      <td>viva la vida</td>\n",
       "      <td>2019-01-29 14:17:15.000</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>22.000</td>\n",
       "      <td>0</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>.willn1</td>\n",
       "      <td>34.570</td>\n",
       "      <td>低客单</td>\n",
       "      <td>viva la vida</td>\n",
       "      <td>2019-01-11 03:46:18.000</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>34.570</td>\n",
       "      <td>0</td>\n",
       "      <td>山东省</td>\n",
       "      <td>烟台市</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>.托托m</td>\n",
       "      <td>37.475</td>\n",
       "      <td>低客单</td>\n",
       "      <td>viva la vida</td>\n",
       "      <td>2019-01-11 02:26:33.000</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>37.475</td>\n",
       "      <td>0</td>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000妮</td>\n",
       "      <td>13.500</td>\n",
       "      <td>低客单</td>\n",
       "      <td>viva la vida</td>\n",
       "      <td>2019-06-28 16:53:26.458</td>\n",
       "      <td>交易成功</td>\n",
       "      <td>13.500</td>\n",
       "      <td>0</td>\n",
       "      <td>广东省</td>\n",
       "      <td>揭阳市</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         买家昵称  实付金额_x 客单类别          品牌名称                    付款日期  订单状态  \\\n",
       "0   .blue_ram  49.450  低客单  viva la vida 2019-02-04 17:49:34.000  交易成功   \n",
       "1  .christiny  22.000  低客单  viva la vida 2019-01-29 14:17:15.000  交易成功   \n",
       "2     .willn1  34.570  低客单  viva la vida 2019-01-11 03:46:18.000  交易成功   \n",
       "3        .托托m  37.475  低客单  viva la vida 2019-01-11 02:26:33.000  交易成功   \n",
       "4       0000妮  13.500  低客单  viva la vida 2019-06-28 16:53:26.458  交易成功   \n",
       "\n",
       "   实付金额_y  邮费   省份   城市  购买数量  \n",
       "0  49.450   0   上海  上海市     1  \n",
       "1  22.000   0  江苏省  南京市     1  \n",
       "2  34.570   0  山东省  烟台市     2  \n",
       "3  37.475   0   上海  上海市     3  \n",
       "4  13.500   0  广东省  揭阳市     1  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#先去重\n",
    "df_dup = df.loc[df.duplicated('买家昵称') == False,:]\n",
    "\n",
    "#再合并\n",
    "df_merge = pd.merge(gp_user,df_dup,left_on = '买家昵称',right_on = '买家昵称',how = 'left')\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用透视表的方法来统计每个省市低客单、高客单人数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">买家昵称</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>客单类别</th>\n",
       "      <th>低客单</th>\n",
       "      <th>高客单</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <th>上海市</th>\n",
       "      <td>2818.0</td>\n",
       "      <td>2374.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">云南省</th>\n",
       "      <th>临沧市</th>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>丽江市</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>保山市</th>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>大理白族自治州</th>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               买家昵称        \n",
       "客单类别            低客单     高客单\n",
       "省份  城市                     \n",
       "上海  上海市      2818.0  2374.0\n",
       "云南省 临沧市         3.0     2.0\n",
       "    丽江市         1.0     3.0\n",
       "    保山市         6.0     2.0\n",
       "    大理白族自治州     9.0     8.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#先筛选出我们需要的列\n",
    "df_merge = df_merge[['买家昵称','客单类别','省份','城市']]\n",
    "\n",
    "#再用透视表\n",
    "result = pd.pivot_table(df_merge,index =['省份','城市'],columns = '客单类别',aggfunc = 'count')\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将低客单和高客单数据转化为我们熟悉的DF格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>高客单</th>\n",
       "      <th>低客单</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>2374.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>云南省</td>\n",
       "      <td>临沧市</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>云南省</td>\n",
       "      <td>丽江市</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>云南省</td>\n",
       "      <td>保山市</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>云南省</td>\n",
       "      <td>大理白族自治州</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    省份       城市     高客单     低客单\n",
       "0   上海      上海市  2374.0  2818.0\n",
       "1  云南省      临沧市     2.0     3.0\n",
       "2  云南省      丽江市     3.0     1.0\n",
       "3  云南省      保山市     2.0     6.0\n",
       "4  云南省  大理白族自治州     8.0     9.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tgi = pd.merge(result['买家昵称']['高客单'].reset_index(),result['买家昵称']['低客单'].reset_index(),\n",
    "               left_on = ['省份','城市'],right_on = ['省份','城市'],how = 'inner')\n",
    "tgi.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算总人数，以及每个城市对应的高客单占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>高客单</th>\n",
       "      <th>低客单</th>\n",
       "      <th>总人数</th>\n",
       "      <th>高客单占比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>2374.0</td>\n",
       "      <td>2818.0</td>\n",
       "      <td>5192.0</td>\n",
       "      <td>0.457242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>云南省</td>\n",
       "      <td>临沧市</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>云南省</td>\n",
       "      <td>丽江市</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>云南省</td>\n",
       "      <td>保山市</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>云南省</td>\n",
       "      <td>大理白族自治州</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.470588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    省份       城市     高客单     低客单     总人数     高客单占比\n",
       "0   上海      上海市  2374.0  2818.0  5192.0  0.457242\n",
       "1  云南省      临沧市     2.0     3.0     5.0  0.400000\n",
       "2  云南省      丽江市     3.0     1.0     4.0  0.750000\n",
       "3  云南省      保山市     2.0     6.0     8.0  0.250000\n",
       "4  云南省  大理白族自治州     8.0     9.0    17.0  0.470588"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tgi['总人数'] = tgi['高客单'] + tgi['低客单']\n",
    "tgi['高客单占比'] = tgi['高客单'] / tgi['总人数']\n",
    "\n",
    "tgi.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 检核数据空值情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 346 entries, 0 to 345\n",
      "Data columns (total 6 columns):\n",
      "省份       346 non-null object\n",
      "城市       346 non-null object\n",
      "高客单      332 non-null float64\n",
      "低客单      329 non-null float64\n",
      "总人数      315 non-null float64\n",
      "高客单占比    315 non-null float64\n",
      "dtypes: float64(4), object(2)\n",
      "memory usage: 18.9+ KB\n"
     ]
    }
   ],
   "source": [
    "tgi.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 去除空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "tgi = tgi.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算总体高客单人数占比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.41528303343887557"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_percentage = tgi['高客单'].sum() / tgi['总人数'].sum()\n",
    "total_percentage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算每个城市高客单TGI指数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>高客单</th>\n",
       "      <th>低客单</th>\n",
       "      <th>总人数</th>\n",
       "      <th>高客单占比</th>\n",
       "      <th>高客单TGI指数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>哈密市</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>192.639702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>巴音郭楞蒙古自治州</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.769231</td>\n",
       "      <td>185.230483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>云南省</td>\n",
       "      <td>丽江市</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>180.599721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>甘肃省</td>\n",
       "      <td>白银市</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>180.599721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>吉林省</td>\n",
       "      <td>辽源市</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>160.533085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>四川省</td>\n",
       "      <td>广安市</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>160.533085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>广西壮族自治区</td>\n",
       "      <td>河池市</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>160.533085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>锡林郭勒盟</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>160.533085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>鹤岗市</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>160.533085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>山西省</td>\n",
       "      <td>临汾市</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.642857</td>\n",
       "      <td>154.799761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           省份         城市   高客单  低客单   总人数     高客单占比    高客单TGI指数\n",
       "149  新疆维吾尔自治区        哈密市   4.0  1.0   5.0  0.800000  192.639702\n",
       "152  新疆维吾尔自治区  巴音郭楞蒙古自治州  10.0  3.0  13.0  0.769231  185.230483\n",
       "2         云南省        丽江市   3.0  1.0   4.0  0.750000  180.599721\n",
       "277       甘肃省        白银市   3.0  1.0   4.0  0.750000  180.599721\n",
       "34        吉林省        辽源市   2.0  1.0   3.0  0.666667  160.533085\n",
       "44        四川省        广安市   6.0  3.0   9.0  0.666667  160.533085\n",
       "136   广西壮族自治区        河池市   4.0  2.0   6.0  0.666667  160.533085\n",
       "25     内蒙古自治区      锡林郭勒盟   2.0  1.0   3.0  0.666667  160.533085\n",
       "343      黑龙江省        鹤岗市   2.0  1.0   3.0  0.666667  160.533085\n",
       "97        山西省        临汾市   9.0  5.0  14.0  0.642857  154.799761"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tgi['高客单TGI指数'] = tgi['高客单占比'] / total_percentage * 100\n",
    "tgi = tgi.sort_values('高客单TGI指数',ascending = False)\n",
    "tgi.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 筛选出人数大于平均值的人数，再计算更合理的TGI指数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>高客单</th>\n",
       "      <th>低客单</th>\n",
       "      <th>总人数</th>\n",
       "      <th>高客单占比</th>\n",
       "      <th>高客单TGI指数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>福建省</td>\n",
       "      <td>福州市</td>\n",
       "      <td>145.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>0.517857</td>\n",
       "      <td>124.699807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>广东省</td>\n",
       "      <td>珠海市</td>\n",
       "      <td>49.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>0.485149</td>\n",
       "      <td>116.823582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>1203.0</td>\n",
       "      <td>1298.0</td>\n",
       "      <td>2501.0</td>\n",
       "      <td>0.481008</td>\n",
       "      <td>115.826450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>福建省</td>\n",
       "      <td>厦门市</td>\n",
       "      <td>105.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>223.0</td>\n",
       "      <td>0.470852</td>\n",
       "      <td>113.380991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>广东省</td>\n",
       "      <td>佛山市</td>\n",
       "      <td>118.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>253.0</td>\n",
       "      <td>0.466403</td>\n",
       "      <td>112.309708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>江西省</td>\n",
       "      <td>南昌市</td>\n",
       "      <td>63.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>136.0</td>\n",
       "      <td>0.463235</td>\n",
       "      <td>111.546887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>四川省</td>\n",
       "      <td>成都市</td>\n",
       "      <td>287.0</td>\n",
       "      <td>334.0</td>\n",
       "      <td>621.0</td>\n",
       "      <td>0.462158</td>\n",
       "      <td>111.287429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海</td>\n",
       "      <td>上海市</td>\n",
       "      <td>2374.0</td>\n",
       "      <td>2818.0</td>\n",
       "      <td>5192.0</td>\n",
       "      <td>0.457242</td>\n",
       "      <td>110.103682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>江苏省</td>\n",
       "      <td>无锡市</td>\n",
       "      <td>135.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>109.454376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>广东省</td>\n",
       "      <td>深圳市</td>\n",
       "      <td>438.0</td>\n",
       "      <td>528.0</td>\n",
       "      <td>966.0</td>\n",
       "      <td>0.453416</td>\n",
       "      <td>109.182440</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      省份   城市     高客单     低客单     总人数     高客单占比    高客单TGI指数\n",
       "287  福建省  福州市   145.0   135.0   280.0  0.517857  124.699807\n",
       "124  广东省  珠海市    49.0    52.0   101.0  0.485149  116.823582\n",
       "27    北京  北京市  1203.0  1298.0  2501.0  0.481008  115.826450\n",
       "283  福建省  厦门市   105.0   118.0   223.0  0.470852  113.380991\n",
       "111  广东省  佛山市   118.0   135.0   253.0  0.466403  112.309708\n",
       "173  江西省  南昌市    63.0    73.0   136.0  0.463235  111.546887\n",
       "46   四川省  成都市   287.0   334.0   621.0  0.462158  111.287429\n",
       "0     上海  上海市  2374.0  2818.0  5192.0  0.457242  110.103682\n",
       "164  江苏省  无锡市   135.0   162.0   297.0  0.454545  109.454376\n",
       "120  广东省  深圳市   438.0   528.0   966.0  0.453416  109.182440"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tgi.loc[tgi['总人数'] > tgi['总人数'].mean(),:].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
